{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "init_cell": true
   },
   "outputs": [],
   "source": [
    "%logstop\n",
    "%logstart -ortq ~/.logs/dw.py append\n",
    "%matplotlib inline\n",
    "import matplotlib\n",
    "import seaborn as sns\n",
    "sns.set()\n",
    "matplotlib.rcParams['figure.dpi'] = 144"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "from static_grader import grader"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# DW Miniproject\n",
    "## Introduction\n",
    "\n",
    "The objective of this miniproject is to exercise your ability to wrangle tabular data set and aggregate large data sets into meaningful summary statistics. We'll work with the same medical data used in the `pw` miniproject but leverage the power of Pandas to more efficiently represent and act on our data."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Downloading the data\n",
    "\n",
    "We first need to download the data we'll be using from Amazon S3:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "mkdir: cannot create directory ‘dw-data’: File exists\n",
      "File ‘./dw-data/201701scripts_sample.csv.gz’ already there; not retrieving.\n",
      "\n",
      "File ‘./dw-data/201606scripts_sample.csv.gz’ already there; not retrieving.\n",
      "\n",
      "File ‘./dw-data/practices.csv.gz’ already there; not retrieving.\n",
      "\n",
      "File ‘./dw-data/chem.csv.gz’ already there; not retrieving.\n",
      "\n"
     ]
    }
   ],
   "source": [
    "!mkdir dw-data\n",
    "!wget http://dataincubator-wqu.s3.amazonaws.com/dwdata/201701scripts_sample.csv.gz -nc -P ./dw-data/\n",
    "!wget http://dataincubator-wqu.s3.amazonaws.com/dwdata/201606scripts_sample.csv.gz -nc -P ./dw-data/\n",
    "!wget http://dataincubator-wqu.s3.amazonaws.com/dwdata/practices.csv.gz -nc -P ./dw-data/\n",
    "!wget http://dataincubator-wqu.s3.amazonaws.com/dwdata/chem.csv.gz -nc -P ./dw-data/"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Loading the data\n",
    "\n",
    "Similar to the `PW` miniproject, the first step is to read in the data. The data files are stored as compressed CSV files. You can load the data into a Pandas DataFrame by making use of the `gzip` package to decompress the files and Panda's `read_csv` methods to parse the data into a DataFrame. You may want to check the Pandas documentation for parsing [CSV](http://pandas.pydata.org/pandas-docs/stable/generated/pandas.read_csv.html) files for reference.\n",
    "\n",
    "For a description of the data set please, refer to the [PW miniproject](./pw.ipynb). **Note that all questions make use of the 2017 data only, except for Question 5 which makes use of both the 2017 and 2016 data.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import gzip"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# type(scripts.quantity)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# load the 2017 data\n",
    "with gzip.open ('./dw-data/201701scripts_sample.csv.gz', 'rb') as f:\n",
    "    scripts = pd.read_csv(f)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "col_names=[ 'code', 'name', 'addr_1', 'addr_2', 'borough', 'village', 'post_code']\n",
    "\n",
    "with gzip.open('./dw-data/practices.csv.gz', 'rb') as f:\n",
    "    practices = pd.read_csv(f, names = col_names)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "with gzip.open('./dw-data/chem.csv.gz', 'rb') as f:\n",
    "    chem = pd.read_csv(f)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>code</th>\n",
       "      <th>name</th>\n",
       "      <th>addr_1</th>\n",
       "      <th>addr_2</th>\n",
       "      <th>borough</th>\n",
       "      <th>village</th>\n",
       "      <th>post_code</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A81001</td>\n",
       "      <td>THE DENSHAM SURGERY</td>\n",
       "      <td>THE HEALTH CENTRE</td>\n",
       "      <td>LAWSON STREET</td>\n",
       "      <td>STOCKTON ON TEES</td>\n",
       "      <td>CLEVELAND</td>\n",
       "      <td>TS18 1HU</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A81002</td>\n",
       "      <td>QUEENS PARK MEDICAL CENTRE</td>\n",
       "      <td>QUEENS PARK MEDICAL CTR</td>\n",
       "      <td>FARRER STREET</td>\n",
       "      <td>STOCKTON ON TEES</td>\n",
       "      <td>CLEVELAND</td>\n",
       "      <td>TS18 2AW</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A81003</td>\n",
       "      <td>VICTORIA MEDICAL PRACTICE</td>\n",
       "      <td>THE HEALTH CENTRE</td>\n",
       "      <td>VICTORIA ROAD</td>\n",
       "      <td>HARTLEPOOL</td>\n",
       "      <td>CLEVELAND</td>\n",
       "      <td>TS26 8DB</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     code                        name                   addr_1         addr_2  \\\n",
       "0  A81001         THE DENSHAM SURGERY        THE HEALTH CENTRE  LAWSON STREET   \n",
       "1  A81002  QUEENS PARK MEDICAL CENTRE  QUEENS PARK MEDICAL CTR  FARRER STREET   \n",
       "2  A81003   VICTORIA MEDICAL PRACTICE        THE HEALTH CENTRE  VICTORIA ROAD   \n",
       "\n",
       "            borough    village post_code  \n",
       "0  STOCKTON ON TEES  CLEVELAND  TS18 1HU  \n",
       "1  STOCKTON ON TEES  CLEVELAND  TS18 2AW  \n",
       "2        HARTLEPOOL  CLEVELAND  TS26 8DB  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "practices.head(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now that we've loaded in the data, let's first replicate our results from the `PW` miniproject. Note that we are now working with a larger data set so the answers will be different than in the `PW` miniproject even if the analysis is the same."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Question 1: summary_statistics\n",
    "\n",
    "In the `PW` miniproject we first calculated the total, mean, standard deviation, and quartile statistics of the `'items'`, `'quantity'`', `'nic'`, and `'act_cost'` fields. To do this we had to write some functions to calculate the statistics and apply the functions to our data structure. The DataFrame has a `describe` method that will calculate most (not all) of these things for us.\n",
    "\n",
    "Submit the summary statistics to the grader as a list of tuples: [('act_cost', (total, mean, std, q25, median, q75)), ...]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "# scripts['items'] = scripts['items'].astype(int, inplace=True)\n",
    "# type(scripts['items'])\n",
    "# scripts.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "# type(scripts['items'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "#convert column items in scripts to series\n",
    "x=pd.Series(scripts['items'])\n",
    "scripts.items=x\n",
    "# x.sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "items_total = scripts.items.sum()\n",
    "items_mean = scripts.items.mean()\n",
    "items_std = scripts.items.std()\n",
    "items_25 = scripts.items.quantile(.25)\n",
    "items_median = scripts.items.quantile(.50)\n",
    "items_q75 = scripts.items.quantile(.75)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "qty_total = scripts.quantity.sum()\n",
    "qty_mean = scripts.quantity.mean()\n",
    "qty_std = scripts.quantity.std()\n",
    "qty_25 = scripts.quantity.quantile(.25)\n",
    "qty_median = scripts.quantity.quantile(.50)\n",
    "qty_q75 = scripts.quantity.quantile(.75)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "# nic = tuple(scripts.nic.describe())\n",
    "nic_total = scripts.nic.sum()\n",
    "nic_mean = scripts.nic.mean()\n",
    "nic_std = scripts.nic.std()\n",
    "nic_q25 = scripts.nic.quantile(.25)\n",
    "nic_median = scripts.nic.quantile(.50)\n",
    "nic_q75 = scripts.nic.quantile(.75)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "# act_cost = tuple(scripts.act_cost.describe())\n",
    "act_cost_total = scripts.act_cost.sum()\n",
    "act_cost_mean = scripts.act_cost.mean()\n",
    "act_cost_std = scripts.act_cost.std()\n",
    "act_cost_q25 = scripts.act_cost.quantile(.25)\n",
    "act_cost_median = scripts.act_cost.quantile(.50)\n",
    "act_cost_q75 = scripts.act_cost.quantile(.75)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "summary_stats=[('items',(items_total,items_mean,items_std,items_25,items_median,items_q75)),\n",
    "               ('quantity',(qty_total,qty_mean,qty_std,qty_25,qty_median,qty_q75)),\n",
    "               ('nic',(nic_total,nic_mean,nic_std,nic_q25,nic_median,nic_q75)),\n",
    "               ('act_cost',(act_cost_total,act_cost_mean,act_cost_std,act_cost_q25,act_cost_median,act_cost_q75))\n",
    "              ]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "# summar_stats = scripts.items.agg({'items': {'sum','mean','std',}})\n",
    "# summar_stats\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "==================\n",
      "Your score:  1.0\n",
      "==================\n"
     ]
    }
   ],
   "source": [
    "grader.score.dw__summary_statistics(summary_stats)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Question 2: most_common_item\n",
    "\n",
    "We can also easily compute summary statistics on groups within the data. In the `pw` miniproject we had to explicitly construct the groups based on the values of a particular field. Pandas will handle that for us via the `groupby` method. This process is [detailed in the Pandas documentation](https://pandas.pydata.org/pandas-docs/stable/groupby.html).\n",
    "\n",
    "Use `groupby` to calculate the total number of items dispensed for each `'bnf_name'`. Find the item with the highest total and return the result as `[(bnf_name, total)]`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "bnf_calc =dict(scripts.groupby('bnf_name')['items'].sum())\n",
    "most_common_item = ['', 0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[('Omeprazole_Cap E/C 20mg', 218583)]\n"
     ]
    }
   ],
   "source": [
    "for k, v in bnf_calc.items():\n",
    "        if v >  most_common_item[1]:\n",
    "            most_common_item[0]= k\n",
    "            most_common_item[1]= v\n",
    "most_common_item = [tuple(most_common_item)]\n",
    "print(most_common_item)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "==================\n",
      "Your score:  1.0\n",
      "==================\n"
     ]
    }
   ],
   "source": [
    "grader.score.dw__most_common_item(most_common_item)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Question 3: items_by_region\n",
    "\n",
    "Now let's find the most common item by post code. The post code information is in the `practices` DataFrame, and we'll need to `merge` it into the `scripts` DataFrame. Pandas provides [extensive documentation](https://pandas.pydata.org/pandas-docs/stable/merging.html) with diagrammed examples on different methods and approaches for joining data. The `merge` method is only one of many possible options.\n",
    "\n",
    "Return your results as a list of tuples `(post code, item name, amount dispensed as % of total)`. Sort your results ascending alphabetically by post code and take only results from the first 100 post codes.\n",
    "\n",
    "**NOTE:** Some practices have multiple postal codes associated with them. Use the alphabetically first postal code. Note some postal codes may have multiple `'bnf_name'` with the same prescription rate for the maximum. In this case, take the alphabetically first `'bnf_name'` (as in the PW miniproject)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>code</th>\n",
       "      <th>name</th>\n",
       "      <th>addr_1</th>\n",
       "      <th>addr_2</th>\n",
       "      <th>borough</th>\n",
       "      <th>village</th>\n",
       "      <th>post_code</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A81001</td>\n",
       "      <td>THE DENSHAM SURGERY</td>\n",
       "      <td>THE HEALTH CENTRE</td>\n",
       "      <td>LAWSON STREET</td>\n",
       "      <td>STOCKTON ON TEES</td>\n",
       "      <td>CLEVELAND</td>\n",
       "      <td>TS18 1HU</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     code                 name             addr_1         addr_2  \\\n",
       "0  A81001  THE DENSHAM SURGERY  THE HEALTH CENTRE  LAWSON STREET   \n",
       "\n",
       "            borough    village post_code  \n",
       "0  STOCKTON ON TEES  CLEVELAND  TS18 1HU  "
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "practices.head(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#checking for duplicate values\n",
    "duplicates = practices['post_code'].duplicated().any()\n",
    "duplicates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "#dropping duplicated values in post_code and sorting by post_code\n",
    "sort_postcode = practices.sort_values('post_code').drop_duplicates(subset='code', keep='first')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>code</th>\n",
       "      <th>name</th>\n",
       "      <th>addr_1</th>\n",
       "      <th>addr_2</th>\n",
       "      <th>borough</th>\n",
       "      <th>village</th>\n",
       "      <th>post_code</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [code, name, addr_1, addr_2, borough, village, post_code]\n",
       "Index: []"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#check if duplicates remain\n",
    "# it would only print header if all duplicated values are gone\n",
    "sort_postcode[sort_postcode['code'].duplicated()].sort_values('code') "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>practice</th>\n",
       "      <th>bnf_code</th>\n",
       "      <th>bnf_name</th>\n",
       "      <th>items</th>\n",
       "      <th>nic</th>\n",
       "      <th>act_cost</th>\n",
       "      <th>quantity</th>\n",
       "      <th>code</th>\n",
       "      <th>name</th>\n",
       "      <th>addr_1</th>\n",
       "      <th>addr_2</th>\n",
       "      <th>borough</th>\n",
       "      <th>village</th>\n",
       "      <th>post_code</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>N85639</td>\n",
       "      <td>0106020C0</td>\n",
       "      <td>Bisacodyl_Tab E/C 5mg</td>\n",
       "      <td>1</td>\n",
       "      <td>0.39</td>\n",
       "      <td>0.47</td>\n",
       "      <td>12</td>\n",
       "      <td>N85639</td>\n",
       "      <td>GP OOH VCH</td>\n",
       "      <td>VICTORIA CENTRAL HOSPITAL</td>\n",
       "      <td>MILL LANE</td>\n",
       "      <td>WALLASEY</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CH44 5UF</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>N85639</td>\n",
       "      <td>0106040M0</td>\n",
       "      <td>Movicol Plain_Paed Pdr Sach 6.9g</td>\n",
       "      <td>1</td>\n",
       "      <td>4.38</td>\n",
       "      <td>4.07</td>\n",
       "      <td>30</td>\n",
       "      <td>N85639</td>\n",
       "      <td>GP OOH VCH</td>\n",
       "      <td>VICTORIA CENTRAL HOSPITAL</td>\n",
       "      <td>MILL LANE</td>\n",
       "      <td>WALLASEY</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CH44 5UF</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  practice   bnf_code                          bnf_name  items   nic  \\\n",
       "0   N85639  0106020C0             Bisacodyl_Tab E/C 5mg      1  0.39   \n",
       "1   N85639  0106040M0  Movicol Plain_Paed Pdr Sach 6.9g      1  4.38   \n",
       "\n",
       "   act_cost  quantity    code        name                     addr_1  \\\n",
       "0      0.47        12  N85639  GP OOH VCH  VICTORIA CENTRAL HOSPITAL   \n",
       "1      4.07        30  N85639  GP OOH VCH  VICTORIA CENTRAL HOSPITAL   \n",
       "\n",
       "      addr_2   borough village post_code  \n",
       "0  MILL LANE  WALLASEY     NaN  CH44 5UF  \n",
       "1  MILL LANE  WALLASEY     NaN  CH44 5UF  "
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#merge scripts to practices\n",
    "merged = scripts.merge(sort_postcode, left_on = 'practice', right_on = 'code')\n",
    "merged.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Note some postal codes may have multiple 'bnf_name' with the same prescription rate for the maximum. \n",
    "#In this case, take the alphabetically first 'bnf_name'\n",
    "\n",
    "items_by_post_code = merged.groupby(['post_code', 'bnf_name'])[['items']].sum()\n",
    "items_by_post_code.reset_index(inplace=True) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "False\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>post_code</th>\n",
       "      <th>items</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>B11 4BW</td>\n",
       "      <td>706</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>B12 9LP</td>\n",
       "      <td>425</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>B18 7AL</td>\n",
       "      <td>556</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>B21 9RY</td>\n",
       "      <td>1033</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>B23 6DJ</td>\n",
       "      <td>599</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  post_code  items\n",
       "0   B11 4BW    706\n",
       "1   B12 9LP    425\n",
       "2   B18 7AL    556\n",
       "3   B21 9RY   1033\n",
       "4   B23 6DJ    599"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "total_max_items = items_by_post_code.groupby('post_code')[['items']].max()\n",
    "total_max_items.reset_index(inplace=True)\n",
    "print(total_max_items.duplicated().any())\n",
    "total_max_items.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "False\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>post_code</th>\n",
       "      <th>items</th>\n",
       "      <th>bnf_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>B11 4BW</td>\n",
       "      <td>706</td>\n",
       "      <td>Salbutamol_Inha 100mcg (200 D) CFF</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>B12 9LP</td>\n",
       "      <td>425</td>\n",
       "      <td>Paracet_Tab 500mg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>B18 7AL</td>\n",
       "      <td>556</td>\n",
       "      <td>Salbutamol_Inha 100mcg (200 D) CFF</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>B21 9RY</td>\n",
       "      <td>1033</td>\n",
       "      <td>Metformin HCl_Tab 500mg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>B23 6DJ</td>\n",
       "      <td>599</td>\n",
       "      <td>Lansoprazole_Cap 30mg (E/C Gran)</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  post_code  items                            bnf_name\n",
       "0   B11 4BW    706  Salbutamol_Inha 100mcg (200 D) CFF\n",
       "1   B12 9LP    425                   Paracet_Tab 500mg\n",
       "2   B18 7AL    556  Salbutamol_Inha 100mcg (200 D) CFF\n",
       "3   B21 9RY   1033             Metformin HCl_Tab 500mg\n",
       "4   B23 6DJ    599    Lansoprazole_Cap 30mg (E/C Gran)"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "total_items = total_max_items.merge(items_by_post_code, on = ['post_code','items'], how = 'left')\n",
    "#check for duplicates\n",
    "print(total_items.duplicated().any())\n",
    "total_items.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>post_code</th>\n",
       "      <th>items</th>\n",
       "      <th>bnf_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>B11 4BW</td>\n",
       "      <td>706</td>\n",
       "      <td>Salbutamol_Inha 100mcg (200 D) CFF</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>B12 9LP</td>\n",
       "      <td>425</td>\n",
       "      <td>Paracet_Tab 500mg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>B18 7AL</td>\n",
       "      <td>556</td>\n",
       "      <td>Salbutamol_Inha 100mcg (200 D) CFF</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>B21 9RY</td>\n",
       "      <td>1033</td>\n",
       "      <td>Metformin HCl_Tab 500mg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>B23 6DJ</td>\n",
       "      <td>599</td>\n",
       "      <td>Lansoprazole_Cap 30mg (E/C Gran)</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  post_code  items                            bnf_name\n",
       "0   B11 4BW    706  Salbutamol_Inha 100mcg (200 D) CFF\n",
       "1   B12 9LP    425                   Paracet_Tab 500mg\n",
       "2   B18 7AL    556  Salbutamol_Inha 100mcg (200 D) CFF\n",
       "3   B21 9RY   1033             Metformin HCl_Tab 500mg\n",
       "4   B23 6DJ    599    Lansoprazole_Cap 30mg (E/C Gran)"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "total_items.sort_values('post_code').head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(259, 2)\n",
      "False\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>post_code</th>\n",
       "      <th>items</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>B11 4BW</td>\n",
       "      <td>22731</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>B12 9LP</td>\n",
       "      <td>17073</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>B18 7AL</td>\n",
       "      <td>20508</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>B21 9RY</td>\n",
       "      <td>31027</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>B23 6DJ</td>\n",
       "      <td>28011</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  post_code  items\n",
       "0   B11 4BW  22731\n",
       "1   B12 9LP  17073\n",
       "2   B18 7AL  20508\n",
       "3   B21 9RY  31027\n",
       "4   B23 6DJ  28011"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "postal_sum = items_by_post_code.groupby('post_code')[['items']].sum()\n",
    "postal_sum.reset_index(inplace = True)\n",
    "print(postal_sum.shape)\n",
    "print(postal_sum.duplicated().any())\n",
    "postal_sum.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>post_code</th>\n",
       "      <th>items_x</th>\n",
       "      <th>items_y</th>\n",
       "      <th>bnf_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>B11 4BW</td>\n",
       "      <td>22731</td>\n",
       "      <td>706</td>\n",
       "      <td>Salbutamol_Inha 100mcg (200 D) CFF</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>B12 9LP</td>\n",
       "      <td>17073</td>\n",
       "      <td>425</td>\n",
       "      <td>Paracet_Tab 500mg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>B18 7AL</td>\n",
       "      <td>20508</td>\n",
       "      <td>556</td>\n",
       "      <td>Salbutamol_Inha 100mcg (200 D) CFF</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>B21 9RY</td>\n",
       "      <td>31027</td>\n",
       "      <td>1033</td>\n",
       "      <td>Metformin HCl_Tab 500mg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>B23 6DJ</td>\n",
       "      <td>28011</td>\n",
       "      <td>599</td>\n",
       "      <td>Lansoprazole_Cap 30mg (E/C Gran)</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  post_code  items_x  items_y                            bnf_name\n",
       "0   B11 4BW    22731      706  Salbutamol_Inha 100mcg (200 D) CFF\n",
       "1   B12 9LP    17073      425                   Paracet_Tab 500mg\n",
       "2   B18 7AL    20508      556  Salbutamol_Inha 100mcg (200 D) CFF\n",
       "3   B21 9RY    31027     1033             Metformin HCl_Tab 500mg\n",
       "4   B23 6DJ    28011      599    Lansoprazole_Cap 30mg (E/C Gran)"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# # #find proportion of total\n",
    "merged2 = postal_sum.merge(total_items, on = 'post_code', how='left')\n",
    "merged2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>post_code</th>\n",
       "      <th>items_x</th>\n",
       "      <th>items_y</th>\n",
       "      <th>bnf_name</th>\n",
       "      <th>prop %</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>B11 4BW</td>\n",
       "      <td>22731</td>\n",
       "      <td>706</td>\n",
       "      <td>Salbutamol_Inha 100mcg (200 D) CFF</td>\n",
       "      <td>0.031059</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>B12 9LP</td>\n",
       "      <td>17073</td>\n",
       "      <td>425</td>\n",
       "      <td>Paracet_Tab 500mg</td>\n",
       "      <td>0.024893</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  post_code  items_x  items_y                            bnf_name    prop %\n",
       "0   B11 4BW    22731      706  Salbutamol_Inha 100mcg (200 D) CFF  0.031059\n",
       "1   B12 9LP    17073      425                   Paracet_Tab 500mg  0.024893"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged2['prop %'] = merged2['items_y'].astype(float)/merged2['items_x'].astype(float)\n",
    "merged2.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(100, 3)\n"
     ]
    }
   ],
   "source": [
    "result = merged2.drop(['items_x', 'items_y'], axis=1).head(100)\n",
    "print(result.shape)\n",
    "values=result.values.tolist()\n",
    "# values[0:10]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "final_result=[]\n",
    "\n",
    "for item in values:\n",
    "    final_result.append(tuple(item))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "items_by_region = final_result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "==================\n",
      "Your score:  1.0\n",
      "==================\n"
     ]
    }
   ],
   "source": [
    "grader.score.dw__items_by_region(items_by_region)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Question 4: script_anomalies\n",
    "\n",
    "Drug abuse is a source of human and monetary costs in health care. A first step in identifying practitioners that enable drug abuse is to look for practices where commonly abused drugs are prescribed unusually often. Let's try to find practices that prescribe an unusually high amount of opioids. The opioids we'll look for are given in the list below."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "opioids = ['morphine', 'oxycodone', 'methadone', 'fentanyl', 'pethidine', 'buprenorphine', 'propoxyphene', 'codeine']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "These are generic names for drugs, not brand names. Generic drug names can be found using the `'bnf_code'` field in `scripts` along with the `chem` table.. Use the list of opioids provided above along with these fields to make a new field in the `scripts` data that flags whether the row corresponds with a opioid prescription."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(3487, 2)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>CHEM SUB</th>\n",
       "      <th>NAME</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0101010A0</td>\n",
       "      <td>Alexitol Sodium</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0101010B0</td>\n",
       "      <td>Almasilate</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0101010C0</td>\n",
       "      <td>Aluminium Hydroxide</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    CHEM SUB                 NAME\n",
       "0  0101010A0      Alexitol Sodium\n",
       "1  0101010B0           Almasilate\n",
       "2  0101010C0  Aluminium Hydroxide"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(chem.shape)\n",
    "chem.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(973193, 7)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>practice</th>\n",
       "      <th>bnf_code</th>\n",
       "      <th>bnf_name</th>\n",
       "      <th>items</th>\n",
       "      <th>nic</th>\n",
       "      <th>act_cost</th>\n",
       "      <th>quantity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>N85639</td>\n",
       "      <td>0106020C0</td>\n",
       "      <td>Bisacodyl_Tab E/C 5mg</td>\n",
       "      <td>1</td>\n",
       "      <td>0.39</td>\n",
       "      <td>0.47</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>N85639</td>\n",
       "      <td>0106040M0</td>\n",
       "      <td>Movicol Plain_Paed Pdr Sach 6.9g</td>\n",
       "      <td>1</td>\n",
       "      <td>4.38</td>\n",
       "      <td>4.07</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>N85639</td>\n",
       "      <td>0301011R0</td>\n",
       "      <td>Salbutamol_Inha 100mcg (200 D) CFF</td>\n",
       "      <td>1</td>\n",
       "      <td>1.50</td>\n",
       "      <td>1.40</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  practice   bnf_code                            bnf_name  items   nic  \\\n",
       "0   N85639  0106020C0               Bisacodyl_Tab E/C 5mg      1  0.39   \n",
       "1   N85639  0106040M0    Movicol Plain_Paed Pdr Sach 6.9g      1  4.38   \n",
       "2   N85639  0301011R0  Salbutamol_Inha 100mcg (200 D) CFF      1  1.50   \n",
       "\n",
       "   act_cost  quantity  \n",
       "0      0.47        12  \n",
       "1      4.07        30  \n",
       "2      1.40         1  "
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(scripts.shape)\n",
    "scripts.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [],
   "source": [
    "chem2 = chem.copy()\n",
    "script2 = scripts.copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'morphine|oxycodone|methadone|fentanyl|pethidine|buprenorphine|propoxyphene|codeine'"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "opioids_join = '|'.join(opioids)\n",
    "opioids_join"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [],
   "source": [
    "opioid_codes = chem.loc[chem['NAME'].str.contains(opioids_join, case=False)]['CHEM SUB'].tolist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>practice</th>\n",
       "      <th>bnf_code</th>\n",
       "      <th>bnf_name</th>\n",
       "      <th>items</th>\n",
       "      <th>nic</th>\n",
       "      <th>act_cost</th>\n",
       "      <th>quantity</th>\n",
       "      <th>opioid_flag</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>N85639</td>\n",
       "      <td>0106020C0</td>\n",
       "      <td>Bisacodyl_Tab E/C 5mg</td>\n",
       "      <td>1</td>\n",
       "      <td>0.39</td>\n",
       "      <td>0.47</td>\n",
       "      <td>12</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>N85639</td>\n",
       "      <td>0106040M0</td>\n",
       "      <td>Movicol Plain_Paed Pdr Sach 6.9g</td>\n",
       "      <td>1</td>\n",
       "      <td>4.38</td>\n",
       "      <td>4.07</td>\n",
       "      <td>30</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>N85639</td>\n",
       "      <td>0301011R0</td>\n",
       "      <td>Salbutamol_Inha 100mcg (200 D) CFF</td>\n",
       "      <td>1</td>\n",
       "      <td>1.50</td>\n",
       "      <td>1.40</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>N85639</td>\n",
       "      <td>0304010G0</td>\n",
       "      <td>Chlorphenamine Mal_Oral Soln 2mg/5ml</td>\n",
       "      <td>1</td>\n",
       "      <td>2.62</td>\n",
       "      <td>2.44</td>\n",
       "      <td>150</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>N85639</td>\n",
       "      <td>0401020K0</td>\n",
       "      <td>Diazepam_Tab 2mg</td>\n",
       "      <td>1</td>\n",
       "      <td>0.16</td>\n",
       "      <td>0.26</td>\n",
       "      <td>6</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  practice   bnf_code                              bnf_name  items   nic  \\\n",
       "0   N85639  0106020C0                 Bisacodyl_Tab E/C 5mg      1  0.39   \n",
       "1   N85639  0106040M0      Movicol Plain_Paed Pdr Sach 6.9g      1  4.38   \n",
       "2   N85639  0301011R0    Salbutamol_Inha 100mcg (200 D) CFF      1  1.50   \n",
       "3   N85639  0304010G0  Chlorphenamine Mal_Oral Soln 2mg/5ml      1  2.62   \n",
       "4   N85639  0401020K0                      Diazepam_Tab 2mg      1  0.16   \n",
       "\n",
       "   act_cost  quantity  opioid_flag  \n",
       "0      0.47        12        False  \n",
       "1      4.07        30        False  \n",
       "2      1.40         1        False  \n",
       "3      2.44       150        False  \n",
       "4      0.26         6        False  "
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "script2['opioid_flag'] = script2['bnf_code'].apply(lambda x: x in opioid_codes )\n",
    "len(script2)\n",
    "script2.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now for each practice calculate the proportion of its prescriptions containing opioids.\n",
    "\n",
    "**Hint:** Consider the following list: `[0, 1, 1, 0, 0, 0]`. What proportion of the entries are 1s? What is the mean value?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "practice\n",
       "A81005    0.033179\n",
       "A81007    0.043329\n",
       "A81011    0.046556\n",
       "A81012    0.042793\n",
       "A81017    0.038140\n",
       "Name: opioid_prop, dtype: float64"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "opioid_prop = script2.groupby('practice')['opioid_flag'].mean().rename('opioid_prop')\n",
    "opioid_prop.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "How do these proportions compare to the overall opioid prescription rate? Subtract off the proportion of all prescriptions that are opioids from each practice's proportion."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.03580276471367961"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "overall_mean = script2['opioid_flag'].mean()\n",
    "mean_diff = (opioid_prop - overall_mean).rename('mean_diff')\n",
    "overall_mean"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "practice\n",
       "A81005   -0.002624\n",
       "A81007    0.007526\n",
       "A81011    0.010753\n",
       "A81012    0.006990\n",
       "A81017    0.002337\n",
       "Name: mean_diff, dtype: float64"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mean_diff.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "practice\n",
       "A81005    0.033179\n",
       "A81007    0.043329\n",
       "A81011    0.046556\n",
       "A81012    0.042793\n",
       "A81017    0.038140\n",
       "Name: opioid_prop, dtype: float64"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "opioid_prop.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now that we know the difference between each practice's opioid prescription rate and the overall rate, we can identify which practices prescribe opioids at above average or below average rates. However, are the differences from the overall rate important or just random deviations? In other words, are the differences from the overall rate big or small?\n",
    "\n",
    "To answer this question we have to quantify the difference we would typically expect between a given practice's opioid prescription rate and the overall rate. This quantity is called the **standard error**, and is related to the **standard deviation**, $\\sigma$. The standard error in this case is\n",
    "\n",
    "$$ \\frac{\\sigma}{\\sqrt{n}} $$\n",
    "\n",
    "where $n$ is the number of prescriptions each practice made. Calculate the standard error for each practice. Then divide `relative_opioids_per_practice` by the standard errors. We'll call the final result `opioid_scores`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0.18579817605238425\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "practice\n",
       "A81005    0.179162\n",
       "A81007    0.203666\n",
       "A81011    0.210753\n",
       "A81012    0.202466\n",
       "A81017    0.191578\n",
       "Name: opioid_std, dtype: float64"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "opioid_std = script2.groupby('practice')['opioid_flag'].std().rename('opioid_std')\n",
    "overall_std = script2['opioid_flag'].std()\n",
    "print(overall_std)\n",
    "opioid_std.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "practice\n",
       "A81005    50.0\n",
       "A81007    63.0\n",
       "A81011    73.0\n",
       "A81012    57.0\n",
       "A81017    82.0\n",
       "Name: opioid_sum, dtype: float64"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "opioid_sum = script2.groupby('practice')['opioid_flag'].sum().rename('opioid_sum')\n",
    "opioid_sum.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "practice\n",
       "A81005    1507\n",
       "A81007    1454\n",
       "A81011    1568\n",
       "A81012    1332\n",
       "A81017    2150\n",
       "Name: opioid_total, dtype: int64"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "opioid_total = script2.groupby('practice')['opioid_flag'].count().rename('opioid_total')\n",
    "opioid_total.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "practice\n",
       "A81005    0.004786\n",
       "A81007    0.004873\n",
       "A81011    0.004692\n",
       "A81012    0.005091\n",
       "A81017    0.004007\n",
       "Name: std_err, dtype: float64"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "standard_error_per_practice = overall_std/(opioid_total**0.5).rename('std_err')\n",
    "standard_error_per_practice.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "practice\n",
       "A81005   -0.548306\n",
       "A81007    1.544557\n",
       "A81011    2.291795\n",
       "A81012    1.373060\n",
       "A81017    0.583168\n",
       "Name: opioid_scores, dtype: float64"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "opioid_scores = (mean_diff/standard_error_per_practice).rename('opioid_scores')\n",
    "opioid_scores.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The quantity we have calculated in `opioid_scores` is called a **z-score**:\n",
    "\n",
    "$$ \\frac{\\bar{X} - \\mu}{\\sqrt{\\sigma^2/n}} $$\n",
    "\n",
    "Here $\\bar{X}$ corresponds with the proportion for each practice, $\\mu$ corresponds with the proportion across all practices, $\\sigma^2$ corresponds with the variance of the proportion across all practices, and $n$ is the number of prescriptions made by each practice. Notice $\\bar{X}$ and $n$ will be different for each practice, while $\\mu$ and $\\sigma$ are determined across all prescriptions, and so are the same for every z-score. The z-score is a useful statistical tool used for hypothesis testing, finding outliers, and comparing data about different types of objects or events.\n",
    "\n",
    "Now that we've calculated this statistic, take the 100 practices with the largest z-score. Return your result as a list of tuples in the form `(practice_name, z-score, number_of_scripts)`. Sort your tuples by z-score in descending order. Note that some practice codes will correspond with multiple names. In this case, use the first match when sorting names alphabetically."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [],
   "source": [
    "merged = pd.concat([opioid_sum, opioid_total, mean_diff, opioid_prop, standard_error_per_practice,opioid_scores], axis =1\n",
    "                ).reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>practice</th>\n",
       "      <th>opioid_sum</th>\n",
       "      <th>opioid_total</th>\n",
       "      <th>mean_diff</th>\n",
       "      <th>opioid_prop</th>\n",
       "      <th>std_err</th>\n",
       "      <th>opioid_scores</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A81005</td>\n",
       "      <td>50.0</td>\n",
       "      <td>1507</td>\n",
       "      <td>-0.002624</td>\n",
       "      <td>0.033179</td>\n",
       "      <td>0.004786</td>\n",
       "      <td>-0.548306</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A81007</td>\n",
       "      <td>63.0</td>\n",
       "      <td>1454</td>\n",
       "      <td>0.007526</td>\n",
       "      <td>0.043329</td>\n",
       "      <td>0.004873</td>\n",
       "      <td>1.544557</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A81011</td>\n",
       "      <td>73.0</td>\n",
       "      <td>1568</td>\n",
       "      <td>0.010753</td>\n",
       "      <td>0.046556</td>\n",
       "      <td>0.004692</td>\n",
       "      <td>2.291795</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A81012</td>\n",
       "      <td>57.0</td>\n",
       "      <td>1332</td>\n",
       "      <td>0.006990</td>\n",
       "      <td>0.042793</td>\n",
       "      <td>0.005091</td>\n",
       "      <td>1.373060</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A81017</td>\n",
       "      <td>82.0</td>\n",
       "      <td>2150</td>\n",
       "      <td>0.002337</td>\n",
       "      <td>0.038140</td>\n",
       "      <td>0.004007</td>\n",
       "      <td>0.583168</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  practice  opioid_sum  opioid_total  mean_diff  opioid_prop   std_err  \\\n",
       "0   A81005        50.0          1507  -0.002624     0.033179  0.004786   \n",
       "1   A81007        63.0          1454   0.007526     0.043329  0.004873   \n",
       "2   A81011        73.0          1568   0.010753     0.046556  0.004692   \n",
       "3   A81012        57.0          1332   0.006990     0.042793  0.005091   \n",
       "4   A81017        82.0          2150   0.002337     0.038140  0.004007   \n",
       "\n",
       "   opioid_scores  \n",
       "0      -0.548306  \n",
       "1       1.544557  \n",
       "2       2.291795  \n",
       "3       1.373060  \n",
       "4       0.583168  "
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>code</th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A81001</td>\n",
       "      <td>THE DENSHAM SURGERY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A81002</td>\n",
       "      <td>QUEENS PARK MEDICAL CENTRE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A81003</td>\n",
       "      <td>VICTORIA MEDICAL PRACTICE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A81004</td>\n",
       "      <td>WOODLANDS ROAD SURGERY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A81005</td>\n",
       "      <td>SPRINGWOOD SURGERY</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     code                        name\n",
       "0  A81001         THE DENSHAM SURGERY\n",
       "1  A81002  QUEENS PARK MEDICAL CENTRE\n",
       "2  A81003   VICTORIA MEDICAL PRACTICE\n",
       "3  A81004      WOODLANDS ROAD SURGERY\n",
       "4  A81005          SPRINGWOOD SURGERY"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pract = practices[['code', 'name']]\n",
    "pract.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>practice</th>\n",
       "      <th>opioid_sum</th>\n",
       "      <th>opioid_total</th>\n",
       "      <th>mean_diff</th>\n",
       "      <th>opioid_prop</th>\n",
       "      <th>std_err</th>\n",
       "      <th>opioid_scores</th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>829</th>\n",
       "      <td>Y01852</td>\n",
       "      <td>6.0</td>\n",
       "      <td>7</td>\n",
       "      <td>0.821340</td>\n",
       "      <td>0.857143</td>\n",
       "      <td>0.070225</td>\n",
       "      <td>11.695818</td>\n",
       "      <td>NATIONAL ENHANCED SERVICE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>874</th>\n",
       "      <td>Y03006</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2</td>\n",
       "      <td>0.964197</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.131379</td>\n",
       "      <td>7.339043</td>\n",
       "      <td>OUTREACH SERVICE NH / RH</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>895</th>\n",
       "      <td>Y03668</td>\n",
       "      <td>11.0</td>\n",
       "      <td>60</td>\n",
       "      <td>0.147531</td>\n",
       "      <td>0.183333</td>\n",
       "      <td>0.023986</td>\n",
       "      <td>6.150582</td>\n",
       "      <td>BRISDOC HEALTHCARE SERVICES OOH</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>296</th>\n",
       "      <td>G81703</td>\n",
       "      <td>7.0</td>\n",
       "      <td>36</td>\n",
       "      <td>0.158642</td>\n",
       "      <td>0.194444</td>\n",
       "      <td>0.030966</td>\n",
       "      <td>5.123032</td>\n",
       "      <td>H&amp;R P C SPECIAL SCHEME</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>948</th>\n",
       "      <td>Y04997</td>\n",
       "      <td>28.0</td>\n",
       "      <td>321</td>\n",
       "      <td>0.051425</td>\n",
       "      <td>0.087227</td>\n",
       "      <td>0.010370</td>\n",
       "      <td>4.958866</td>\n",
       "      <td>HMR BARDOC OOH</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    practice  opioid_sum  opioid_total  mean_diff  opioid_prop   std_err  \\\n",
       "829   Y01852         6.0             7   0.821340     0.857143  0.070225   \n",
       "874   Y03006         2.0             2   0.964197     1.000000  0.131379   \n",
       "895   Y03668        11.0            60   0.147531     0.183333  0.023986   \n",
       "296   G81703         7.0            36   0.158642     0.194444  0.030966   \n",
       "948   Y04997        28.0           321   0.051425     0.087227  0.010370   \n",
       "\n",
       "     opioid_scores                             name  \n",
       "829      11.695818        NATIONAL ENHANCED SERVICE  \n",
       "874       7.339043         OUTREACH SERVICE NH / RH  \n",
       "895       6.150582  BRISDOC HEALTHCARE SERVICES OOH  \n",
       "296       5.123032           H&R P C SPECIAL SCHEME  \n",
       "948       4.958866                   HMR BARDOC OOH  "
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_df = merged.merge(pract, left_on='practice', right_on='code', how='left').drop('code', axis =1)\n",
    "final_df.sort_values('opioid_scores', ascending = False , inplace=True)\n",
    "final_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [],
   "source": [
    "unique_practices = final_df.drop_duplicates('name', keep='first')\n",
    "anomalies = [(\"NATIONAL ENHANCED SERVICE\", 11.6958178629, 7)] * 100"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>practice</th>\n",
       "      <th>opioid_sum</th>\n",
       "      <th>opioid_total</th>\n",
       "      <th>mean_diff</th>\n",
       "      <th>opioid_prop</th>\n",
       "      <th>std_err</th>\n",
       "      <th>opioid_scores</th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>829</th>\n",
       "      <td>Y01852</td>\n",
       "      <td>6.0</td>\n",
       "      <td>7</td>\n",
       "      <td>0.821340</td>\n",
       "      <td>0.857143</td>\n",
       "      <td>0.070225</td>\n",
       "      <td>11.695818</td>\n",
       "      <td>NATIONAL ENHANCED SERVICE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>874</th>\n",
       "      <td>Y03006</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2</td>\n",
       "      <td>0.964197</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.131379</td>\n",
       "      <td>7.339043</td>\n",
       "      <td>OUTREACH SERVICE NH / RH</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>895</th>\n",
       "      <td>Y03668</td>\n",
       "      <td>11.0</td>\n",
       "      <td>60</td>\n",
       "      <td>0.147531</td>\n",
       "      <td>0.183333</td>\n",
       "      <td>0.023986</td>\n",
       "      <td>6.150582</td>\n",
       "      <td>BRISDOC HEALTHCARE SERVICES OOH</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>296</th>\n",
       "      <td>G81703</td>\n",
       "      <td>7.0</td>\n",
       "      <td>36</td>\n",
       "      <td>0.158642</td>\n",
       "      <td>0.194444</td>\n",
       "      <td>0.030966</td>\n",
       "      <td>5.123032</td>\n",
       "      <td>H&amp;R P C SPECIAL SCHEME</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>948</th>\n",
       "      <td>Y04997</td>\n",
       "      <td>28.0</td>\n",
       "      <td>321</td>\n",
       "      <td>0.051425</td>\n",
       "      <td>0.087227</td>\n",
       "      <td>0.010370</td>\n",
       "      <td>4.958866</td>\n",
       "      <td>HMR BARDOC OOH</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    practice  opioid_sum  opioid_total  mean_diff  opioid_prop   std_err  \\\n",
       "829   Y01852         6.0             7   0.821340     0.857143  0.070225   \n",
       "874   Y03006         2.0             2   0.964197     1.000000  0.131379   \n",
       "895   Y03668        11.0            60   0.147531     0.183333  0.023986   \n",
       "296   G81703         7.0            36   0.158642     0.194444  0.030966   \n",
       "948   Y04997        28.0           321   0.051425     0.087227  0.010370   \n",
       "\n",
       "     opioid_scores                             name  \n",
       "829      11.695818        NATIONAL ENHANCED SERVICE  \n",
       "874       7.339043         OUTREACH SERVICE NH / RH  \n",
       "895       6.150582  BRISDOC HEALTHCARE SERVICES OOH  \n",
       "296       5.123032           H&R P C SPECIAL SCHEME  \n",
       "948       4.958866                   HMR BARDOC OOH  "
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "unique_practices.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>opioid_scores</th>\n",
       "      <th>opioid_total</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>829</th>\n",
       "      <td>NATIONAL ENHANCED SERVICE</td>\n",
       "      <td>11.695818</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>874</th>\n",
       "      <td>OUTREACH SERVICE NH / RH</td>\n",
       "      <td>7.339043</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>895</th>\n",
       "      <td>BRISDOC HEALTHCARE SERVICES OOH</td>\n",
       "      <td>6.150582</td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>296</th>\n",
       "      <td>H&amp;R P C SPECIAL SCHEME</td>\n",
       "      <td>5.123032</td>\n",
       "      <td>36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>948</th>\n",
       "      <td>HMR BARDOC OOH</td>\n",
       "      <td>4.958866</td>\n",
       "      <td>321</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                name  opioid_scores  opioid_total\n",
       "829        NATIONAL ENHANCED SERVICE      11.695818             7\n",
       "874         OUTREACH SERVICE NH / RH       7.339043             2\n",
       "895  BRISDOC HEALTHCARE SERVICES OOH       6.150582            60\n",
       "296           H&R P C SPECIAL SCHEME       5.123032            36\n",
       "948                   HMR BARDOC OOH       4.958866           321"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result= unique_practices[['name', 'opioid_scores', 'opioid_total']]\n",
    "result.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[['NATIONAL ENHANCED SERVICE', 11.695817862936027, 7],\n",
       " ['OUTREACH SERVICE NH / RH', 7.339043019238823, 2]]"
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result_list=result.values.tolist()\n",
    "result_list[:2]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [],
   "source": [
    "anomal = []\n",
    "for i in result_list[:100]:\n",
    "    anomal.append(tuple(i))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "==================\n",
      "Error!\n",
      "You have been rate limited for exceeding the limit of 3 per 1 minute.\n",
      "Please slow down your submission rate.\n",
      "==================\n"
     ]
    }
   ],
   "source": [
    "grader.score.dw__script_anomalies(anomal)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Question 5: script_growth\n",
    "\n",
    "Another way to identify anomalies is by comparing current data to historical data. In the case of identifying sites of drug abuse, we might compare a practice's current rate of opioid prescription to their rate 5 or 10 years ago. Unless the nature of the practice has changed, the profile of drugs they prescribe should be relatively stable. We might also want to identify trends through time for business reasons, identifying drugs that are gaining market share. That's what we'll do in this question.\n",
    "\n",
    "We'll load in beneficiary data from 6 months earlier, June 2016, and calculate the percent growth in prescription rate from June 2016 to January 2017 for each `bnf_name`. We'll return the 50 items with largest growth and the 50 items with the largest shrinkage (i.e. negative percent growth) as a list of tuples sorted by growth rate in descending order in the format `(script_name, growth_rate, raw_2016_count)`. You'll notice that many of the 50 fastest growing items have low counts of prescriptions in 2016. Filter out any items that were prescribed less than 50 times."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 145,
   "metadata": {},
   "outputs": [],
   "source": [
    "# load the 2016 data\n",
    "with gzip.open ('./dw-data/201606scripts_sample.csv.gz', 'rb') as f:\n",
    "    scripts16 = pd.read_csv(f)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 146,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>practice</th>\n",
       "      <th>bnf_code</th>\n",
       "      <th>bnf_name</th>\n",
       "      <th>items</th>\n",
       "      <th>nic</th>\n",
       "      <th>act_cost</th>\n",
       "      <th>quantity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>N85638</td>\n",
       "      <td>0301011R0</td>\n",
       "      <td>Salamol_Inha 100mcg (200 D) CFF (Teva)</td>\n",
       "      <td>2</td>\n",
       "      <td>2.92</td>\n",
       "      <td>2.73</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>N85638</td>\n",
       "      <td>0301011R0</td>\n",
       "      <td>Easyhaler_Salbutamol Sulf 200mcg (200D)</td>\n",
       "      <td>1</td>\n",
       "      <td>6.63</td>\n",
       "      <td>6.15</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>N85638</td>\n",
       "      <td>0301020I0</td>\n",
       "      <td>Ipratrop Brom_Inh Soln 500mcg/2ml Ud</td>\n",
       "      <td>1</td>\n",
       "      <td>1.77</td>\n",
       "      <td>1.75</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>N85638</td>\n",
       "      <td>0301020I0</td>\n",
       "      <td>Ipratrop Brom_Inh Soln 250mcg/1ml Ud</td>\n",
       "      <td>1</td>\n",
       "      <td>4.47</td>\n",
       "      <td>4.15</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>N85638</td>\n",
       "      <td>0302000C0</td>\n",
       "      <td>Clenil Modulite_Inha 50mcg (200D)</td>\n",
       "      <td>1</td>\n",
       "      <td>3.70</td>\n",
       "      <td>3.44</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  practice   bnf_code                                 bnf_name  items   nic  \\\n",
       "0   N85638  0301011R0   Salamol_Inha 100mcg (200 D) CFF (Teva)      2  2.92   \n",
       "1   N85638  0301011R0  Easyhaler_Salbutamol Sulf 200mcg (200D)      1  6.63   \n",
       "2   N85638  0301020I0     Ipratrop Brom_Inh Soln 500mcg/2ml Ud      1  1.77   \n",
       "3   N85638  0301020I0     Ipratrop Brom_Inh Soln 250mcg/1ml Ud      1  4.47   \n",
       "4   N85638  0302000C0        Clenil Modulite_Inha 50mcg (200D)      1  3.70   \n",
       "\n",
       "   act_cost  quantity  \n",
       "0      2.73         2  \n",
       "1      6.15         1  \n",
       "2      1.75        12  \n",
       "3      4.15        20  \n",
       "4      3.44         1  "
      ]
     },
     "execution_count": 146,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "script06 = scripts16.copy()\n",
    "script06.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 147,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>practice</th>\n",
       "      <th>bnf_code</th>\n",
       "      <th>bnf_name</th>\n",
       "      <th>items</th>\n",
       "      <th>nic</th>\n",
       "      <th>act_cost</th>\n",
       "      <th>quantity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>N85639</td>\n",
       "      <td>0106020C0</td>\n",
       "      <td>Bisacodyl_Tab E/C 5mg</td>\n",
       "      <td>1</td>\n",
       "      <td>0.39</td>\n",
       "      <td>0.47</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>N85639</td>\n",
       "      <td>0106040M0</td>\n",
       "      <td>Movicol Plain_Paed Pdr Sach 6.9g</td>\n",
       "      <td>1</td>\n",
       "      <td>4.38</td>\n",
       "      <td>4.07</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>N85639</td>\n",
       "      <td>0301011R0</td>\n",
       "      <td>Salbutamol_Inha 100mcg (200 D) CFF</td>\n",
       "      <td>1</td>\n",
       "      <td>1.50</td>\n",
       "      <td>1.40</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>N85639</td>\n",
       "      <td>0304010G0</td>\n",
       "      <td>Chlorphenamine Mal_Oral Soln 2mg/5ml</td>\n",
       "      <td>1</td>\n",
       "      <td>2.62</td>\n",
       "      <td>2.44</td>\n",
       "      <td>150</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>N85639</td>\n",
       "      <td>0401020K0</td>\n",
       "      <td>Diazepam_Tab 2mg</td>\n",
       "      <td>1</td>\n",
       "      <td>0.16</td>\n",
       "      <td>0.26</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  practice   bnf_code                              bnf_name  items   nic  \\\n",
       "0   N85639  0106020C0                 Bisacodyl_Tab E/C 5mg      1  0.39   \n",
       "1   N85639  0106040M0      Movicol Plain_Paed Pdr Sach 6.9g      1  4.38   \n",
       "2   N85639  0301011R0    Salbutamol_Inha 100mcg (200 D) CFF      1  1.50   \n",
       "3   N85639  0304010G0  Chlorphenamine Mal_Oral Soln 2mg/5ml      1  2.62   \n",
       "4   N85639  0401020K0                      Diazepam_Tab 2mg      1  0.16   \n",
       "\n",
       "   act_cost  quantity  \n",
       "0      0.47        12  \n",
       "1      4.07        30  \n",
       "2      1.40         1  \n",
       "3      2.44       150  \n",
       "4      0.26         6  "
      ]
     },
     "execution_count": 147,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "script07= scripts.copy()\n",
    "script07.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 165,
   "metadata": {},
   "outputs": [],
   "source": [
    "#calculate growth rate\n",
    "growth_rate = (script07.bnf_name.value_counts() - script06.bnf_name.value_counts())/script06.bnf_name.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 173,
   "metadata": {},
   "outputs": [],
   "source": [
    "growth_frame = pd.DataFrame(dict(growth_ = growth_rate, yr2016 = script06.bnf_name.value_counts())).reset_index()\n",
    "growth_frame.fillna(0, inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 176,
   "metadata": {},
   "outputs": [],
   "source": [
    "#  Filter out any items that were prescribed less than 50 times.\n",
    "ahead_50=growth_frame[growth_frame['yr2016']>=50]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 182,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/opt/conda/lib/python3.7/site-packages/ipykernel_launcher.py:2: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  \n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>index</th>\n",
       "      <th>growth_</th>\n",
       "      <th>yr2016</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1985</th>\n",
       "      <td>Butec_Transdermal Patch 5mcg/hr</td>\n",
       "      <td>3.467742</td>\n",
       "      <td>62.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1983</th>\n",
       "      <td>Butec_Transdermal Patch 10mcg/hr</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>69.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5742</th>\n",
       "      <td>Fostair NEXThaler_Inh 200mcg/6mcg (120D)</td>\n",
       "      <td>1.430233</td>\n",
       "      <td>86.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11270</th>\n",
       "      <td>Pneumococcal_Vac 0.5ml Vl (23 Valent)</td>\n",
       "      <td>1.269430</td>\n",
       "      <td>193.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13357</th>\n",
       "      <td>Spiolto Respimat_Inha2.5/2.5mcg(60D)+Dev</td>\n",
       "      <td>1.269231</td>\n",
       "      <td>52.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                          index   growth_  yr2016\n",
       "1985            Butec_Transdermal Patch 5mcg/hr  3.467742    62.0\n",
       "1983           Butec_Transdermal Patch 10mcg/hr  3.000000    69.0\n",
       "5742   Fostair NEXThaler_Inh 200mcg/6mcg (120D)  1.430233    86.0\n",
       "11270     Pneumococcal_Vac 0.5ml Vl (23 Valent)  1.269430   193.0\n",
       "13357  Spiolto Respimat_Inha2.5/2.5mcg(60D)+Dev  1.269231    52.0"
      ]
     },
     "execution_count": 182,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#sort by growth_rate\n",
    "ahead_50.sort_values(by='growth_', ascending = False, inplace=True)\n",
    "ahead_50.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 179,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('Butec_Transdermal Patch 5mcg/hr', 3.467741935483871, 62.0),\n",
       " ('Butec_Transdermal Patch 10mcg/hr', 3.0, 69.0),\n",
       " ('Fostair NEXThaler_Inh 200mcg/6mcg (120D)', 1.430232558139535, 86.0)]"
      ]
     },
     "execution_count": 179,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Pick the top 50 and last 50 and get the tuple list\n",
    "script_growth = list(pd.concat([ahead_50.head(50), ahead_50.tail(50)]).itertuples(index=False, name=None))\n",
    "script_growth[:3]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 180,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "==================\n",
      "Your score:  1.0\n",
      "==================\n"
     ]
    }
   ],
   "source": [
    "grader.score.dw__script_growth(script_growth)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Question 6: rare_scripts\n",
    "\n",
    "Does a practice's prescription costs originate from routine care or from reliance on rarely prescribed treatments? Commonplace treatments can carry lower costs than rare treatments because of efficiencies in large-scale production. While some specialist practices can't help but avoid prescribing rare medicines because there are no alternatives, some practices may be prescribing a unnecessary amount of brand-name products when generics are available. Let's identify practices whose costs disproportionately originate from rarely prescribed items.\n",
    "\n",
    "First we have to identify which `'bnf_code'` are rare. To do this, find the probability $p$ of a prescription having a particular `'bnf_code'` if the `'bnf_code'` was randomly chosen from the unique options in the beneficiary data. We will call a `'bnf_code'` rare if it is prescribed at a rate less than $0.1p$."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 183,
   "metadata": {},
   "outputs": [],
   "source": [
    "practices_unique = practices.sort_values(['code', 'post_code']).drop_duplicates(subset='code')\n",
    "\n",
    "group_by_bnf_code = scripts.groupby(['bnf_code'])['bnf_code'].count().reset_index(name='bnf_code_count')\n",
    "group_by_bnf_code['p'] = group_by_bnf_code['bnf_code_count'] / len(scripts)\n",
    "px = 1 / len(group_by_bnf_code)\n",
    "\n",
    "group_by_bnf_code['rare'] = np.where(group_by_bnf_code['p'] < 0.1 * px, True, False)\n",
    "scripts_rare = pd.merge(scripts, group_by_bnf_code, how = 'inner', on = 'bnf_code')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now for each practice, calculate the proportion of costs that originate from prescription of rare treatments (i.e. rare `'bnf_code'`). Use the `'act_cost'` field for this calculation."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 184,
   "metadata": {},
   "outputs": [],
   "source": [
    "total_cost_by_practice = scripts_rare.groupby('practice')['act_cost'].sum().reset_index(name = 'total_cost')\n",
    "\n",
    "rare_cost_by_practice = scripts_rare[scripts_rare['rare'] == True].groupby(\n",
    "    'practice')['act_cost'].sum().reset_index(name = 'rare_cost')\n",
    "\n",
    "script_cost_by_practice = pd.merge(total_cost_by_practice, rare_cost_by_practice, on = 'practice', how='left')\n",
    "\n",
    "script_cost_by_practice.fillna(0, inplace=True)\n",
    "\n",
    "script_cost_by_practice['rare_cost_prop'] = (\n",
    "    script_cost_by_practice['rare_cost'] / script_cost_by_practice['total_cost'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we will calculate a z-score for each practice based on this proportion.\n",
    "First take the difference of `rare_cost_prop` and the proportion of costs originating from rare treatments across all practices."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 185,
   "metadata": {},
   "outputs": [],
   "source": [
    "total_cost = scripts_rare['act_cost'].sum()\n",
    "total_rare_cost = scripts_rare[scripts_rare['rare'] == True]['act_cost'].sum()\n",
    "\n",
    "overall_rare_cost = total_rare_cost / total_cost\n",
    "script_cost_by_practice['relative_rare_cost_prop'] = script_cost_by_practice['rare_cost_prop'] - overall_rare_cost"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we will estimate the standard errors (i.e. the denominator of the z-score) by simply taking the standard deviation of this difference."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 186,
   "metadata": {},
   "outputs": [],
   "source": [
    "script_cost_by_practice['standard_errors'] = script_cost_by_practice['relative_rare_cost_prop'].std()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Finally compute the z-scores. Return the practices with the top 100 z-scores in the form `(post_code, practice_name, z-score)`. Note that some practice codes will correspond with multiple names. In this case, use the first match when sorting names alphabetically."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 187,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(856, 7)\n",
      "(856, 9)\n"
     ]
    }
   ],
   "source": [
    "script_cost_by_practice['z_scores'] = script_cost_by_practice['relative_rare_cost_prop'] / script_cost_by_practice['standard_errors']\n",
    "print(script_cost_by_practice.shape)\n",
    "practices_unique = practices.sort_values('name').groupby('code', sort=False).first()\n",
    "practices_unique.reset_index(inplace=True)\n",
    "merge_script_practice_z_scores = script_cost_by_practice.merge(\n",
    "    practices_unique[['name', 'code']], how = 'inner', left_on = 'practice', right_on = 'code', sort = False)\n",
    "\n",
    "print(merge_script_practice_z_scores.shape)\n",
    "merge_script_practice_z_scores = merge_script_practice_z_scores.sort_values('name', ascending = True)\n",
    "merge_script_practice_z_scores.drop_duplicates(subset = ['code', 'name'], inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 188,
   "metadata": {},
   "outputs": [],
   "source": [
    "rare_scripts = []\n",
    "for index, row in merge_script_practice_z_scores.iterrows():\n",
    "    rare_scripts.append(\n",
    "        (row['practice'], \n",
    "         row['name'],\n",
    "         row['z_scores']))\n",
    "\n",
    "rare_scripts = sorted(rare_scripts, key=lambda x: x[2], reverse = True)[:100]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 189,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "==================\n",
      "Your score:  1.0\n",
      "==================\n"
     ]
    }
   ],
   "source": [
    "grader.score.dw__rare_scripts(rare_scripts)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*Copyright &copy; 2019 The Data Incubator.  All rights reserved.*"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.3"
  },
  "nbclean": true
 },
 "nbformat": 4,
 "nbformat_minor": 1
}
